package com.hexu.event.dao;

import com.hexu.event.entity.Memcon;
import com.hexu.event.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MemconDao {

    //保存用户的申诉建议和管理员的回复
    public boolean addContent(Memcon memcon){
        String sql = "insert into memcon(user_phone_number,reply,content)"
                + " values (?,?,?);";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            //View层set的数据
            ps.setString(1, memcon.getPhoneNumber());
            ps.setString(2, memcon.getReply());
            ps.setString(3, memcon.getContent());

            ps.executeUpdate();

            //添加成功
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JdbcUtil.close(conn, ps, null);
        }

        //添加失败
        return false;
    }
    //保存管理员的回复
    public boolean addReply(Memcon memcon){
        String sql = "update memcon set reply=? where user_phone_number=? and content=?";

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            //View层set的数据
            ps.setString(1, memcon.getReply());
            ps.setString(2, memcon.getPhoneNumber());
            ps.setString(3, memcon.getContent());
            ps.executeUpdate();

            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            JdbcUtil.close(conn, ps, null);
        }

        return false;
    }

    //返回所有用户的申诉建议和管理员的回复
    public List<Memcon> getMemconInformation(){
        List<Memcon> list = new ArrayList<>();
        String sql = "select * from memcon";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Memcon memcon = new Memcon();
                memcon.setId(rs.getInt("id"));
                memcon.setPhoneNumber(rs.getString("user_phone_number"));
                memcon.setReply(rs.getString("reply"));
                memcon.setContent(rs.getString("content"));

                list.add(memcon);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return null;
    }

    //返回用户申诉建议的数量
    public int memconNumber(){
        String sql = "select user_phone_number from memcon ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            //使游标可滚动
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();

            // 将游标移动到最后一行上
            rs.last();
            //获取景点数
            int memconCount = rs.getRow();
            //把光标移至第一行前
            rs.beforeFirst();

            return memconCount;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return 0;
    }

    //返回单个用户申诉建议及管理员回复的所有信息
    public List<Memcon> getUserMencon(String phoneNumber){
        List<Memcon> list = new ArrayList<>();
        String sql = "select * from memcon where user_phone_number=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, phoneNumber);
            rs = ps.executeQuery();
            while (rs.next()){
                Memcon memcon = new Memcon();
                memcon.setId(rs.getInt("id"));
                memcon.setPhoneNumber(rs.getString("user_phone_number"));
                memcon.setReply(rs.getString("reply"));
                memcon.setContent(rs.getString("content"));

                list.add(memcon);
            }
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return null;
    }

    //返回单个用户申诉建议的数量
    public int memconUserNumber(String phoneNumber){
        String sql = "select user_phone_number from memcon where user_phone_number=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            //使游标可滚动
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ps.setString(1, phoneNumber);
            rs = ps.executeQuery();

            // 将游标移动到最后一行上
            rs.last();
            //获取景点数
            int memconCount = rs.getRow();
            //把光标移至第一行前
            rs.beforeFirst();

            return memconCount;

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(conn, ps, rs);
        }
        return 0;
    }

}
